<?php

namespace app\admin\service\dev;

use app\admin\model\dev\Field;
use app\admin\model\dev\Menu;
use app\common\Constants;
use think\facade\Config;
use think\facade\Db;
use think\facade\Log;
use think\helper\Str;

class DatabaseService
{


    /**
     * 从数据库表获取字段列表
     * @param $menuId
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function fromTableToFieldList($menuId)
    {
        // 获取菜单信息
        $menuInfo = Db::name('dev_menu')->where('id', $menuId)->find();
        if (!$menuInfo || empty($menuInfo['table_name'])) {
            throw new \Exception("菜单ID {$menuId} 不存在或未配置数据库表名");
        }

        // 获取表字段信息
        $prefix = Config::get("database.connections.{$menuInfo['connect']}.prefix");
        $tableName = $prefix . $menuInfo['table_name'];
        try {
            $columns = Db::query("SHOW FULL COLUMNS FROM `{$tableName}`");
        } catch (\Exception $e) {
            throw new \Exception("无法读取表 {$tableName} 的字段信息: " . $e->getMessage());
        }

        // 获取系统字段的预定义信息
        static $raDevPropData = null;
        if ($raDevPropData === null) {
            $raDevPropData = Db::name('dev_prop')
                ->where('type', 'field')
                ->column('config', 'value'); // 以value为键，config为值
        }

        // 获取字段key的前缀
        $baseKey = str_replace('.title', '', $menuInfo['key']);

        // 处理每个字段的信息
        $menuFields = [];
        foreach ($columns as $column) {
            if (in_array($column['Field'], ['delete_time', 'update_id', 'delete_id', 'create_dept'])) {
                continue;
            }
            // 基本信息
            $fieldName = $column['Field'];
            $fieldComment = $column['Comment'];
            $fieldType = $column['Type']; // 例如: varchar(255), int(11)
            //$fieldNull = $column['Null']; // YES/NO
            //$fieldDefault = $column['Default'];

            // 提取字段类型和长度
            preg_match('/^([a-zA-Z]+)(?:\((\d+)(?:,\d+)?\))?/', $fieldType, $matches);
            //$dataType = $matches[1] ?? $fieldType; // 例如: varchar, int
            //$dataLength = $matches[2] ?? null; // 例如: 255, 11

            // 没有注释，则采用默认
            $commentParts = [];
            if (!$fieldComment) {
                $title = $fieldName;
                $configData = json_decode($raDevPropData['input'], true);
                $config = $configData['config'] ?? []; // 取出config字段中的config部分
            } else {
                // 解析注释
                $commentParts = explode('|', $fieldComment);
                $title = $commentParts[0] ?? $fieldName; // 注释第一部分作为title，如果没有则用字段名
                $configValue = $commentParts[1] ?? ''; // 注释第二部分用于查找config
                // 获得配置
                $config = [];
                if (!empty($configValue) && isset($raDevPropData[$configValue])) {
                    $configData = json_decode($raDevPropData[$configValue], true);
                    $config = $configData['config'] ?? []; // 取出config字段中的config部分
                }
            }
            // 基础字段信息
            $fieldInfo = [
                'menu_id' => $menuId,
                'title' => $title,
                'key' => $baseKey . '.field.' . $fieldName,
                'field' => $fieldName,
                'buildin' => 0,
                'config' => $config,
                'remark' => $title, // remark和title一样
                //'data_type' => $dataType,
                //'data_length' => $dataLength,
                //'field_null' => $fieldNull,
                //'field_default' => $fieldDefault,
            ];
            // 补齐第三部分及后面的配置选项
            if (count($commentParts) > 2) {
                $fieldInfo = self::processCommentParts($fieldInfo, $commentParts);
            }
            $menuFields[] = $fieldInfo;
        }
        return $menuFields;
    }

    /**
     * 处理注释
     * @param $fieldInfo
     * @param $commentParts
     * @return mixed
     */
    private static function processCommentParts($fieldInfo, $commentParts)
    {
        $config = $fieldInfo['config'];
        $part2 = isset($commentParts[2]) && !empty(trim($commentParts[2])) ? trim($commentParts[2]) : null;
        $part3 = isset($commentParts[3]) && !empty(trim($commentParts[3])) ? trim($commentParts[3]) : null;
        $part4 = isset($commentParts[4]) && !empty(trim($commentParts[4])) ? trim($commentParts[4]) : null;

        switch ($config['type']) {
            case 'input_number':
            case 'input':
                $config['item_required'] = $part2 ?: null;
                break;
            case 'textarea':
                $config['item_rows'] = $part2 ?: null;
                break;
            case 'random':
                $config['item_random_length'] = $part2 ?: null;
                break;
            case 'order_sn':
                $config['item_sn_format'] = $part2 ?: null;
                break;
            case 'tree_select':
            case 'select':
            case 'transfer':
            case 'radio':
            case 'checkbox':
            case 'switch':
                $config['item_options_type'] = $part2 ?: null;
                if ($part2 == 'key_value') {
                    if ($part3) {
                        $items = explode(',', $part3);
                        $result = [];
                        foreach ($items as $item) {
                            $parts = explode(':', $item);
                            if (count($parts) === 2) {
                                $result[] = [
                                    'label' => $parts[1],
                                    'value' => (int)$parts[0] // 转换为整数
                                ];
                            }
                        }
                    }
                    $config['item_options'] = $part3 ? $result : null;
                } else {
                    $config['item_options'] = $part3 ?: null;
                }
                $config['item_multiple'] = $part4 ?: null;
                break;
            case 'create_time':
            case 'update_time':
            case 'datetime':
            case 'date_range':
                $config['item_date_type'] = $part2 ?: null;
                $config['item_date_format'] = $part3 ?: null;
                break;
            case 'image_upload':
                $config['item_image_upload'] = $part2 ?: null;
                $config['item_multiple'] = $part3 ?: null;
                $config['item_max_count'] = $part4 ?: null;
                break;
            case 'file_upload':
                $config['item_file_upload'] = $part2 ?: null;
                $config['item_multiple'] = $part3 ?: null;
                $config['item_max_count'] = $part4 ?: null;
                break;
            case 'location_select':
                $config['item_map'] = $part2 ?: null;
                break;
            case 'rich_text_editor':
                $config['item_editor'] = $part2 ?: null;
                $config['item_editor_height'] = $part3 ?: null;
                break;
            case 'list_select':
                $config['item_multiple'] = $part2 ?: null;
                break;
            // case 'hidden':
            case 'password':
            case 'slider':
            case 'client_ip':
            case 'input_tag':
            case 'color_picker':
            case 'key_value':
            case 'token_value':
            case 'create_id':
            case 'ssq_select':
                break;
        }

        $fieldInfo['config'] = $config;
        return $fieldInfo;
    }

    /**
     * 根据数据库表生成菜单的信息
     * @param $table_name
     * @param $connection
     * @param $level
     * @return array|null
     */
    public static function fromTableToMenuInfo(string $table_name, string $connection, int $level = 1): ?array
    {
        $db = Db::connect($connection);

        // 获取表注释
        $full_table_name = $db->name($table_name)->getTable();
        $db_nanme = $db->getConfig('database');
        $sql = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        $table_info = $db->query($sql, [$db_nanme, $full_table_name]);
        $table_comment = Str::studly($table_name);
        if (!empty($table_info) && !empty($table_info[0]['TABLE_COMMENT'])) {
            $table_comment = $table_info[0]['TABLE_COMMENT'];
        }

        // 准备数据
        $parts = explode('_', $table_name);
        if ($level >= count($parts)) {
            return null;
        }
        // 拆分目录部分和实体部分
        $dir_parts = array_slice($parts, 0, $level);
        $entity_parts = array_slice($parts, $level);

        // Controller: 'Demo/Test/FromTable'
        $dir_parts = array_map([Str::class, 'studly'], $dir_parts);
        $dir = implode('/', $dir_parts);
        $controller_parts = array_map([Str::class, 'studly'], $entity_parts);
        $controller = $dir . '/' . implode('', $controller_parts);

        // route_name: 'DemoTestFromTable'
        $route_name = Str::studly($table_name);

        // route_path: 'from-table' 实体部分用短横线连接
        $route_path = implode('-', $entity_parts);

        // route_path_full: '/demo/test/from-table' 目录部分用斜杠连接，然后接上实体路径
        $route_path_full = '/' . implode('/', $dir_parts) . '/' . $route_path;

        // component: 'demo/test/from-table/index' 目录部分用斜杠连接，实体部分也用斜杠连接
        $component = implode('/', $dir_parts) . '/' . implode('-', $entity_parts) . '/index';

        // Code: 'demo:test:from-table:*' 录部分用冒号连接，实体部分用短横线连接，最后两者用冒号连接
        $code = implode(':', $dir_parts) . ':' . implode('-', $entity_parts) . ':*';

        // key: 'menu.demo.test.from-table.title'
        $key = 'menu.' . implode('.', $dir_parts) . '.' . implode('-', $entity_parts) . '.title';

        $data = [
            'pid' => Constants::MENU_ROOT_ID,
            'name' => $table_comment,
            'code' => strtolower($code),
            'permission' => Constants::MENU_PARAMS_YES,
            'type' => Constants::MENU_TYPE_MENU,
            'is_sys' => Constants::MENU_PARAMS_NO,
            'controller' => $controller,
            'route_name' => $route_name,
            'route_path' => $route_path,
            'route_path_full' => strtolower($route_path_full),
            'component' => $component,
            'template' => 'table',
            'always_show' => Constants::MENU_PARAMS_NO,
            'keep_alive' => Constants::MENU_PARAMS_YES,
            'visible' => Constants::MENU_PARAMS_YES,
            'icon' => 'menu',
            'connect' => $connection,
            'table_name' => $table_name,
            'primary_key' => 'id',
            'lang' => Constants::MENU_PARAMS_NO,
            'key' => strtolower($key),
            'app' => 'admin',
            'gen_code' => Constants::MENU_PARAMS_YES,
            'gen_table' => Constants::MENU_PARAMS_YES,
            'remark' => $table_comment,
        ];
        return $data;
    }

    /**
     * 检查表中是否包含内置字段
     * 备份原表并更新表到满足要求的状态
     * @param string $table_name
     * @param string|null $connection
     * @return bool
     */
    public static function checkRepaireTable(string $table_name, string $connection = null): bool
    {
        // 获取数据库连接对象
        $db = Db::connect($connection);

        // 检查表是否存在
        $table_name_full = $db->name($table_name)->getTable();
        $table_exists = $db->query("SHOW TABLES LIKE '{$table_name_full}'");
        if (!$table_exists) {
            return false;
        }

        // 备份表
        $table_name_backup = $table_name_full . '_' . date('YmdHis');
        $db->execute("CREATE TABLE `{$table_name_backup}` LIKE `{$table_name_full}`;");
        $db->execute("INSERT INTO `{$table_name_backup}` SELECT * FROM `{$table_name_full}`;");

        // 检查表的主键
        $primary_key = $db->query("SHOW KEYS FROM `{$table_name_full}` WHERE Key_name = 'PRIMARY'");
        if (empty($primary_key)) {
            // 没有主键，检查是否存在 id 字段
            $columns = $db->query("SHOW COLUMNS FROM `{$table_name_full}` LIKE 'id'");
            if (empty($columns)) {
                // 如果 id 字段也不存在，则创建 id 字段并设为主键
                $sql = "ALTER TABLE `{$table_name_full}` ADD COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;";
                $db->execute($sql);
            } else {
                // 如果 id 字段存在但不是主键，则将其修改为主键
                $sql = "ALTER TABLE `{$table_name_full}` ADD PRIMARY KEY (`id`);";
                $db->execute($sql);
                // 确保 id 是自增的
                $sql_modify = "ALTER TABLE `{$table_name_full}` MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT;";
                $db->execute($sql_modify);
            }
        }

        // 检查缺失的字段
        $fields_to_create = [
            'status' => "TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态|switch|1:正常,0:禁用'",
            'sort' => "INT(11) NOT NULL DEFAULT 0 COMMENT '排序|input_number'",
            'create_id' => "INT(11) NULL DEFAULT NULL COMMENT '创建人ID|input'",
            'create_time' => "DATETIME NULL DEFAULT NULL COMMENT '创建时间|datetime'",
            'update_time' => "DATETIME NULL DEFAULT NULL COMMENT '更新时间|datetime'",
            'create_dept' => "INT(11) NULL DEFAULT NULL COMMENT '创建部门'",
            'update_id' => "INT(11) NULL DEFAULT NULL COMMENT '更新人ID'",
            'delete_id' => "INT(11) NULL DEFAULT NULL COMMENT '删除人ID'",
            'delete_time' => "DATETIME NULL DEFAULT NULL COMMENT '删除时间'",
        ];
        $existing_fields = $db->getTableFields($table_name_full);
        trace($existing_fields, '检查缺失的字段2');
        foreach ($fields_to_create as $field_name => $field_definition) {
            if (!in_array($field_name, $existing_fields)) {
                $sql = "ALTER TABLE `{$table_name_full}` ADD COLUMN `{$field_name}` {$field_definition};";
                $db->execute($sql);
            }
        }
        return true;
    }

    /**
     * 判断表是否存在
     * @param string $table_name
     * @param string $connect
     * @return string
     */
    public static function hasTable(string $table_name, string $connect = ''): string
    {
        // 检查是否提供了完整的 表名
        $prefix = self::getConnectPrefix($connect);
        if (!str_starts_with($table_name, $prefix)) {
            $full_table_name = $prefix . $table_name;
        } else {
            $full_table_name = $table_name;
        }
        try {
            $result = Db::connect($connect)->query("SHOW TABLES LIKE '{$full_table_name}';");
            return !empty($result);
        } catch (\Exception $e) {
            trace([
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'error_message' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            return false;
        }
    }

    public static function hasField(
        string $tableName,
        string $fieldName,
        string $connect = 'mysql'
    ): bool
    {
        // 检查是否提供了完整的 表名
        $prefix = self::getConnectPrefix($connect);
        if (!str_starts_with($tableName, $prefix)) {
            $full_table_name = $prefix . $tableName;
        } else {
            $full_table_name = $tableName;
        }
        $field_list = Db::getTableFields($full_table_name);
        if (in_array($fieldName, $field_list)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 重命名字段
     * @param $ids
     * @return bool
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function renameField($ids): bool
    {
        $timestamp = date("YmdHis");
        $field_list = Field::whereIn('id', $ids)->select()->toArray();
        $menu_info = Menu::find($field_list[0]['menu_id'])->toArray();
        foreach ($field_list as $field) {
            // 只有已经存在此字段，才去修改
            if (self::hasField($menu_info['table_name'], $field['field'], $menu_info['connect'])) {
                $old = ['field' => $field['field'],];
                $new = ['field' => $field['field'] . '_' . $timestamp,];

                $table_prefix = self::getConnectPrefix($menu_info['connect']);
                $tableName = $table_prefix . $menu_info['table_name'];
                $sql = "ALTER TABLE `{$tableName}` RENAME COLUMN `{$old['field']}` TO `{$new['field']}`";
                try {
                    DB::execute($sql);
                } catch (\Exception $e) {
                    trace([
                        'file' => $e->getFile(),
                        'line' => $e->getLine(),
                        'error_message' => $e->getMessage(),
                        'trace' => $e->getTraceAsString()
                    ], 'debug');
                    throw new \Exception($e->getMessage());
                }
            }
        }
        return true;
    }

    public static function renameTable($menuInfo, $timestamp, $rollback = false): bool
    {
        try {
            $table_prefix = self::getConnectPrefix($menuInfo['connect']);
            $table_name = $table_prefix . $menuInfo['table_name'];
            $table_name_new = $table_name . "_" . $timestamp;
            if ($rollback) {
                Db::query("RENAME TABLE `{$table_name_new}` TO `{$table_name}`;");
            } else {
                Db::query("RENAME TABLE `{$table_name}` TO `{$table_name_new}`;");
            }
            return true;
        } catch (\Exception $e) {
            trace([
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'error_message' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ], 'debug');
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 直接删除表的字段
     * @param $menuInfo
     * @param $field_name
     * @return true
     * @throws \Exception
     */
    public static function deleteField($menuInfo, $field_name): bool
    {
        $table_prefix = self::getConnectPrefix($menuInfo['connect']);
        $tableName = $table_prefix . $menuInfo['table_name'];
        $sql = "ALTER TABLE `{$tableName}` DROP COLUMN {$field_name}";
        try {
            DB::execute($sql);
            return true;
        } catch (\Exception $e) {
            trace([
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'error_message' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ], 'debug');
            throw new \Exception($e->getMessage());
        }
    }


    /**
     * 删除菜单时菜单对应的表改名
     * @param $menuInfo
     * @return bool
     * @throws \Exception
     */
    public static function renameTable_v1($menuInfo): bool
    {
        try {
            $timestamp = date("YmdHis");
            $table_prefix = self::getConnectPrefix($menuInfo['connect']);
            $table_name = $table_prefix . $menuInfo['table_name'];
            $table_name_new = $table_name . "_" . $timestamp;
            Db::query("RENAME TABLE `{$table_name}` TO `{$table_name_new}`;");
            return true;
        } catch (\Exception $e) {
            trace([
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'error_message' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ], 'debug');
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 新建或者修改字段
     * [
     * 'menu_id' => 68,
     * 'field' => 'create_dept',
     * 'field_type' => 'int',
     * 'field_length' => 11,
     * 'field_index' => 'none',
     * 'field_constraint' => [],
     * 'field_default' => '',
     * ];
     * @return void
     */
    public static function createOrUpdateTableField(
        array  $menuInfo,
        string $afterField,
        array  $newFieldConfig,
        array  $oldFieldConfig = [])
    {
        try {
            $sqlStatements = [];
            $table_prefix = self::getConnectPrefix($menuInfo['connect']);
            $tableName = $table_prefix . $menuInfo['table_name'];
            // 如果新旧配置一样，则不执行任何操作
            if ($newFieldConfig == $oldFieldConfig) {
                trace('字段配置一样，无需执行任何操作');
                return true;
            }
            $afterExpress = "AFTER `{$afterField}`";
            // 辅助函数：构建列定义
            $buildColumnDefinition = function (array $config) {
                $definition = "`{$config['field']}` {$config['field_type']}";
                if (!empty($config['field_length'])) {
                    $definition .= "({$config['field_length']})";
                }
                if (isset($config['field_constraint']) && in_array('notnull', $config['field_constraint'])) {
                    $definition .= " NOT NULL";
                } else {
                    $definition .= " NULL";
                }

                $pdo = Db::getPdo();
                if (array_key_exists('field_default', $config)) {
                    $value = $config['field_default'];

                    // 使用 match 表达式根据类型进行格式化
                    $formattedDefault = match (gettype($value)) {
                        'NULL'    => 'NULL',
                        'boolean' => $value ? '1' : '0',
                        'integer',
                        'double'  => (string)$value,
                        'string'  => $pdo->quote($value), // 安全转义并加引号
                        default   => null, // 其他类型（如 array, object）不支持，返回 null
                    };

                    if ($formattedDefault !== null) {
                        $definition .= " DEFAULT {$formattedDefault}";
                    }
                }

//                if (isset($config['field_default'])) {
//                    $defaultValue = $config['field_default'];
//                    if (is_string($defaultValue)) {
//                        $defaultValue = "'" . $defaultValue . "'";
//                    } elseif (is_bool($defaultValue)) {
//                        $defaultValue = $defaultValue ? '1' : '0';
//                    }
//                    $definition .= " DEFAULT {$defaultValue}";
//                }

                if (isset($config['field_comment']) && $config['field_comment']) {
                    $definition .= " COMMENT '{$config['field_comment']}'";
                }
                return $definition;
            };

            // 辅助函数：获取索引名
            $getIndexName = function (string $table, string $field, string $type) {
                return "idx_{$table}_{$field}"; // 简化索引名生成规则
            };

            // 主键字段不需要创建了，因为主键字段是自动生成的
            if ($newFieldConfig['field'] === $menuInfo['primary_key']) {
                return true;
            }

            // 如果旧配置为空，则表示新增字段
            if (
                empty($oldFieldConfig) || // 旧配置为空
                !isset($oldFieldConfig["field"]) || // 旧配置字段不存在
                !self::hasField($menuInfo['table_name'], $oldFieldConfig['field'], $menuInfo['connect']) // 表中不存在此字段
            ) {
                $columnDefinition = $buildColumnDefinition($newFieldConfig);
                $sqlStatements[] = "ALTER TABLE `{$tableName}` ADD COLUMN {$columnDefinition} {$afterExpress};";

                // 处理新字段的索引
                if (isset($newFieldConfig["field_index"]) && $newFieldConfig["field_index"] !== 'none') {
                    $indexName = $getIndexName($tableName, $newFieldConfig["field"], $newFieldConfig["field_index"]);
                    if ($newFieldConfig["field_index"] === 'unique') {
                        $sqlStatements[] = "ALTER TABLE `{$tableName}` ADD UNIQUE INDEX `{$indexName}` (`{$newFieldConfig["field"]}`);";
                    }
                    if ($newFieldConfig["field_index"] === 'index') {
                        $sqlStatements[] = "CREATE INDEX `{$indexName}` ON `{$tableName}` (`{$newFieldConfig["field"]}`);";
                    }
                }
                trace($sqlStatements, '生成的新建字段的SQL语句');
                foreach ($sqlStatements as $sql) {
                    Db::query($sql);
                }
                return true;
            }

            $oldField = $oldFieldConfig['field'];
            $newField = $newFieldConfig['field'];
            // 获取当前时间戳，用于旧表备份
            $timestamp = date("YmdHis");

            // 检查是否需要重命名旧字段并添加新字段（不兼容修改）
            $incompatibleChange = false;
            if ($oldFieldConfig['field_type'] !== $newFieldConfig['field_type']) {
                $incompatibleChange = true;
            } elseif (isset($oldFieldConfig['field_length']) && isset($newFieldConfig['field_length'])) {
                // 长度缩短视为不兼容
                if ((int)$oldFieldConfig['field_length'] > (int)$newFieldConfig['field_length']) {
                    $incompatibleChange = true;
                }
            }

            if ($incompatibleChange) {
                // 重命名旧字段
                $oldFieldConfig['field'] = "{$oldField}_{$timestamp}";
                // 字段定义信息
                $columnDefinition = $buildColumnDefinition($oldFieldConfig);
                // 生成改名SQL
                $sqlStatements[] = "ALTER TABLE `{$tableName}` CHANGE COLUMN `{$oldField}` {$columnDefinition};";

                // 添加新字段
                $columnDefinition = $buildColumnDefinition($newFieldConfig);
                $sqlStatements[] = "ALTER TABLE `{$tableName}` ADD COLUMN {$columnDefinition} {$afterExpress};";

                // 处理新字段的索引
                if (isset($newFieldConfig['field_index']) && $newFieldConfig['field_index'] !== 'none') {
                    $indexName = $getIndexName($tableName, $newField, $newFieldConfig['field_index']);
                    if ($newFieldConfig['field_index'] === 'unique') {
                        $sqlStatements[] = "ALTER TABLE `{$tableName}` ADD UNIQUE INDEX `{$indexName}` (`{$newField}`);";
                    }
                    if ($newFieldConfig['field_index'] === 'index') {
                        $sqlStatements[] = "CREATE INDEX `{$indexName}` ON `{$tableName}` (`{$newField}`);";
                    }
                }
            } else {
                // 兼容性修改或仅索引/约束/默认值变化
                if ($oldFieldConfig['field'] === $newFieldConfig['field']) {
                    $columnDefinition = $buildColumnDefinition($newFieldConfig);
                    $sqlStatements[] = "ALTER TABLE `{$tableName}` MODIFY COLUMN {$columnDefinition};";
                } else {
                    $sqlStatements[] = "ALTER TABLE `{$tableName}` RENAME COLUMN `{$oldFieldConfig['field']}` TO `{$newFieldConfig['field']}`";
                }
                // 处理索引变化
                $oldIndex = $oldFieldConfig['field_index'] === 'none' ? null : $oldFieldConfig['field_index'];
                $newIndex = $newFieldConfig['field_index'] === 'none' ? null : $newFieldConfig['field_index'];

                if ($oldIndex && !$newIndex) {
                    // 旧有索引，新无索引：删除旧索引
                    $indexName = $getIndexName($tableName, $oldField, $oldIndex);
                    $sqlStatements[] = "DROP INDEX `{$indexName}` ON `{$tableName}`;";
                } elseif (!$oldIndex && $newIndex) {
                    // 旧无索引，新有索引：添加新索引
                    $indexName = $getIndexName($tableName, $newField, $newIndex);
                    if ($newIndex === 'unique') {
                        $sqlStatements[] = "ALTER TABLE `{$tableName}` ADD UNIQUE INDEX `{$indexName}` (`{$newField}`);";
                    } else {
                        $sqlStatements[] = "CREATE INDEX `{$indexName}` ON `{$tableName}` (`{$newField}`);";
                    }
                } elseif ($oldIndex && $newIndex && $oldIndex !== $newIndex) {
                    // 索引类型变化：先删除旧索引，再添加新索引
                    $oldIndexName = $getIndexName($tableName, $oldField, $oldIndex);
                    $sqlStatements[] = "DROP INDEX `{$oldIndexName}` ON `{$tableName}`;";

                    $newIndexName = $getIndexName($tableName, $newField, $newIndex);
                    if ($newIndex === 'unique') {
                        $sqlStatements[] = "ALTER TABLE `{$tableName}` ADD UNIQUE INDEX `{$newIndexName}` (`{$newField}`);";
                    } else {
                        $sqlStatements[] = "CREATE INDEX `{$newIndexName}` ON `{$tableName}` (`{$newField}`);";
                    }
                }
            }
            trace($sqlStatements, '生成的修改字段的SQL语句');
            foreach ($sqlStatements as $sql) {
                Db::query($sql);
            }
            return true;
        } catch (\Exception $e) {
            trace(dirname(__FILE__) . DIRECTORY_SEPARATOR . basename(__FILE__) . "::createOrUpdateTableField \r\n" . $e->getMessage(), 'error');
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 同步数据库表
     * 当新建或者修改菜单信息时，自动检查数据库表的参数，自行修改数据库表
     * @param $new_config
     * @param $old_config
     * @return array|void
     * @throws \Exception
     */
    public static function syncDatabaseTable($new_config, $old_config = [])
    {
        try {
            $sqls = [];
            $old_connect = $old_config["connect"] ?? "";
            $new_connect = $new_config["connect"] ?? "";
            $table_prefix = self::getConnectPrefix($new_connect);
            $old_table_name = isset($old_config["table_name"]) ? $table_prefix . $old_config["table_name"] : "";
            $new_table_name = $table_prefix . $new_config["table_name"] ?? "";
            $old_primary_key = $old_config["primary_key"] ?? "";
            $new_primary_key = $new_config["primary_key"] ?? "";

            // 新建表
            if (count($old_config) === 0 || empty($old_config)) {
                $sql = "CREATE TABLE `{$new_table_name}` (";
                $sql .= "`{$new_primary_key}` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,";
                $sql .= "PRIMARY KEY (`{$new_primary_key}`)";
                $sql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
                $sqls[] = $sql;
            } else {
                // 不支持更新连接
                if ($old_connect !== $new_connect) {
                    return false;
                }
                // 获取当前时间戳，用于旧表备份
                $timestamp = date("YmdHis");
                // 如果新旧配置完全相同，则不生成SQL
                if ($old_table_name === $new_table_name && $old_primary_key === $new_primary_key) {
                    return false;
                }
                if ($old_table_name !== $new_table_name) {
                    if (!empty($old_table_name) && !empty($new_table_name)) {
                        if ($old_primary_key !== $new_primary_key) {
                            // 表名，主键 都发生变化
                            // 复制旧表到新名，并复制原主键列到新的主键列
                            $sqls[] = "CREATE TABLE `{$new_table_name}` AS SELECT `{$old_primary_key}` AS `{$new_primary_key}`,`{$old_table_name}`.* FROM `{$old_table_name}`;";
                            // 新表增加主键
                            $sqls[] = "ALTER TABLE `{$new_table_name}` ADD PRIMARY KEY (`{$new_primary_key}`);";
                            // 删除新表中原来的主键列
                            $sqls[] = "ALTER TABLE `{$new_table_name}` DROP COLUMN `{$old_primary_key}`;";
                        } else {
                            // 仅表名 发生变化（需要设置主键和自增主键）
                            $sqls[] = "CREATE TABLE `{$new_table_name}` AS SELECT * FROM `{$old_table_name}`;";
                            $sqls[] = "ALTER TABLE `{$new_table_name}` MODIFY `{$old_primary_key}` INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`{$old_primary_key}`);";
                        }
                        // 备份旧表: 如果是从别的表拷贝来的，则复制，否则重命名
                        if ($new_config['from_id']) {
                            $sqls[] = "CREATE TABLE `{$old_table_name}_{$timestamp}` SELECT * FROM `{$old_table_name}`";
                        } else {
                            $sqls[] = "RENAME TABLE `{$old_table_name}` TO `{$old_table_name}_{$timestamp}`;";
                        }
                    }
                } else {
                    if ($old_primary_key !== $new_primary_key) {
                        // 表名不变，主键发生变化
                        // 创建临时表
                        $sqls[] = "CREATE TABLE `temp_table` AS SELECT {$old_primary_key} AS `{$new_primary_key}`, `{$old_table_name}`.*   FROM `{$old_table_name}`;";
                        // 备份旧表
                        $sqls[] = "RENAME TABLE `{$old_table_name}` TO `{$old_table_name}_{$timestamp}`;";
                        // 给临时表添加主键
                        $sqls[] = "ALTER TABLE `temp_table` ADD PRIMARY KEY (`{$new_primary_key}`);";
                        // 删除临时表原主键列
                        $sqls[] = "ALTER TABLE `temp_table` DROP COLUMN `{$old_primary_key}`;";
                        // 将临时表重命名为原表名
                        $sqls[] = "RENAME TABLE `temp_table` TO `{$old_table_name}`;";
                    }
                }
            }

            // 最终要执行的SQL语句数组
            trace($sqls, '需要执行的SQL语句数组');
            foreach ($sqls as $sql) {
                Db::query($sql);
            }
            return true;
        } catch (\Exception $e) {
            trace([
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'error_message' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ], 'debug');
            throw $e;
        }
    }


    /******************************************************************************************************************/


    private static function getConnectPrefix(string $connectName = ''): string
    {
        $tablePrefix = '';
        if ($connectName) {
            $tablePrefix = Config::get("database.connections.{$connectName}.prefix");
        }
        return $tablePrefix;
    }





//    /**
//     * 删除数据库表
//     *
//     * @param $menuData
//     * @return true
//     */
//    public static function rollbackDatabaseTable($menuData): bool
//    {
//        $prefix = self::getConnectPrefix($menuData["connect"]);
//        $sql = "DROP TABLE IF EXISTS `{$prefix}{$menuData["table_name"]}`;";
//        Db::query($sql);
//        return true;
//    }
}